package drugdealer.data;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import drugdealer.exception.UserTypeException;
import drugdealer.model.Paciente;


/**
 * Helper class to manage DB related functions for UsuarioDados table
 * and Paciente object.
 * 
 * @author dave
 */
public class PacienteDAO {
	/**
	 * Static method to insert a new pacient into the database.
	 * This method does not check the validity of data in "Paciente" object
	 * passed as parameter "paciente".
	 * 
	 * @param paciente is the user to be added.
	 * @return different than 0 if successful, 0 otherwise.
	 * @throws UserTypeException 
	 */
	public static int insert(Paciente paciente) throws UserTypeException {
		// Get ConnectionPool
		ConnectionPool pool = ConnectionPool.getInstance();

		// Open DB connection
		Connection connection = pool.getConnection();

		// PreparedStatement contains a query template
		// which can be dynamically populated
		PreparedStatement ps = null;

		// Query templates
		// For UsuarioDados
		String queryUsuarioDados = "INSERT INTO UsuarioDados (Email, Nome, RG, Endereco, Aniversario)"
				+ "VALUES (?, ?, ?, ?, ?)";
		
		// For UsuarioSenha
		String queryUsuarioSenha = "INSERT INTO UsuarioSenha (Email, Senha)"
				+ "VALUES (?, ?)";
		
		// For UsuarioSenha
		String queryUsuarioTipo = "INSERT INTO UsuarioTipo (Email, Tipo)"
				+ "VALUES (?, ?)";

		try {
			int result = 0;
			
			/**************
			  UsuarioDados
			 **************/
			// Populate the query template for UsuarioDados table
			ps = connection.prepareStatement(queryUsuarioDados);
			ps.setString(1, paciente.getEmail());
			ps.setString(2, paciente.getNome());
			ps.setString(3, paciente.getRG());
			ps.setString(4, paciente.getEndereco());
			ps.setDate(5, paciente.getAniversario());

			// WARNING: "aniversario" is java.sql.Date type NOT java.util.Date
			// TODO The date must be formatted into string format "yyyy-mm-dd"
			ps.setDate(5, (Date) paciente.getAniversario());

			// Insert data into UsuarioDados table
			result += ps.executeUpdate();
			
			/**************
			  UsuarioSenha
			 **************/
			// Populate the query template for UsuarioSenha table
			ps = connection.prepareStatement(queryUsuarioSenha);
			ps.setString(1, paciente.getEmail());
			ps.setString(2, paciente.getSenha());
			
			// Insert data into UsuarioSenha table
			result += ps.executeUpdate();
			
			/*************
			  UsuarioTipo
			 *************/
			// Populate the query template for UsuarioSenha table
			ps = connection.prepareStatement(queryUsuarioTipo);
			ps.setString(1, paciente.getEmail());
			ps.setString(2, "paciente");

			// Insert data into UsuarioSenha table
			result += ps.executeUpdate();
			
			return result;
		} catch (SQLException sqle) {
			// todo exceptions throwing
			sqle.printStackTrace();
			return 0;
		} finally {
			pool.freeConnection(connection);
		}
	}

	/**
	 * Static method to get  the list of the pacients whose name field
	 * in the DB contains the string input by the user.
	 * 
	 * @param nomePaciente is the string given by user as search term.
	 * @return ArrayList of Paciente objects
	 *             whose name contains given string if search is positive.
	 *         null otherwise.
	 *         
	 * @throws UserTypeException
	 */
	public static ArrayList<Paciente> buscaPaciente(String nomePaciente) throws UserTypeException {
		// DB connection set up
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			ArrayList<Paciente> pacientes = new ArrayList<Paciente>();
			
			// Query templates		
			String query = "SELECT * FROM UsuarioDados WHERE Nome LIKE CONCAT('%', ? ,'%')"; //LIKE '%?%'";
			
			// Set query string with pacient name
			ps = connection.prepareStatement(query);			
			ps.setString(1, nomePaciente);
			
			// Execute query to retrieve pacients list from DB
			rs = ps.executeQuery();
			
			// Create Pacient object for each patient in the list
			// then add the object into ArrayList
			while(rs.next()) {
				Paciente p = new Paciente();
				p.setEmail(rs.getString("email"));
				p.setEndereco(rs.getString("endereco"));
				p.setNome(rs.getString("nome"));
				p.setRG(rs.getString("RG"));
				p.setAniversario(rs.getDate("aniversario"));
				
				pacientes.add(p);
			}
			
			return pacientes;
		} catch (SQLException sqle) {
			// todo exceptions throwing
			sqle.printStackTrace();
			return null;
		} finally {
			pool.freeConnection(connection);
		}
	}

	
	/**
	 * Static method to tie a pacient to a medic in the Database.
	 * 
	 * @param emailPaciente email address of the pacient.
	 * @param emailMedico email address of the medic.
	 * @return > 0 if successful, 0 if otherwise.
	 */
	public static int adicionaMedico(String emailPaciente, String emailMedico) {
		// DB connection set up
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		
		PreparedStatement ps = null;
		
		// Query template
		String queryUsuarioDados = "INSERT INTO PacienteMedico (EmailPaciente, EmailMedico)"
				+ "VALUES (?, ?)";
		
		try {
			ps = connection.prepareStatement(queryUsuarioDados);
			ps.setString(1, emailPaciente);
			ps.setString(2, emailMedico);
			
			// Execute query, if successful it returns > 0
			return ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			pool.freeConnection(connection);
		}
		
		return 0;
	}
	
	/**
	 * Static method to retrieve an ArrayList of all the Paciente
	 * object tied to a given medic.
	 * 
	 * @param email email address of the medic.
	 * @return ArrayList of Paciente objects.
	 * @throws UserTypeException 
	 */
	public static ArrayList<Paciente> listarPacientes(String email) throws UserTypeException {
		// DB connection set up
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			ArrayList<Paciente> pacientes = new ArrayList<Paciente>();
			
			// Query template
			String query = "SELECT * FROM " +
					"usuariodados INNER JOIN pacientemedico " +
					"ON usuariodados.email=pacientemedico.emailpaciente " +
					"WHERE EmailMedico=?";
			
			// Set query string with pacient name
			ps = connection.prepareStatement(query);			
			ps.setString(1, email);
			
			// Execute query to retrieve pacients list from DB
			rs = ps.executeQuery();
			
			// Create Pacient object for each patient in the list
			// then add the object into ArrayList
			while(rs.next()) {
				Paciente p = new Paciente();
				p.setEmail(rs.getString("email"));
				p.setEndereco(rs.getString("endereco"));
				p.setNome(rs.getString("nome"));
				p.setRG(rs.getString("RG"));
				p.setAniversario(rs.getDate("aniversario"));
				
				pacientes.add(p);
			}
			
			return pacientes;
		} catch (SQLException sqle) {
			// todo exceptions throwing
			sqle.printStackTrace();
			return null;
		} finally {
			pool.freeConnection(connection);
		}
	}
}
